1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmProductRecord
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("Select PID, RTRIM(ProductCode),RTRIM(Productname), SubCategoryID,RTRIM(CategoryName),RTRIM(SubCategoryName), RTRIM(Description), CostPrice,SellingPrice, Discount, VAT, ReorderPoint from Category,SubCategory,Product where Category.CategoryName=SubCategory.Category and Product.SubCategoryID=SubCategory.ID order by ProductName", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 End Sub
25
26
27 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
28 Try
29 If dgw.Rows.Count > 0 Then
30
31 If lblSet.Text = "Product Entry" Then
32 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
33 frmProduct.Show()
34 Me.Hide()
35 frmProduct.txtID.Text = dr.Cells(0).Value.ToString()
36 frmProduct.txtProductCode.Text = dr.Cells(1).Value.ToString()
37 frmProduct.txtProductName.Text = dr.Cells(2).Value.ToString()
38 frmProduct.txtSubCategoryID.Text = dr.Cells(3).Value.ToString()
39 frmProduct.cmbCategory.Text = dr.Cells(4).Value.ToString()
40 frmProduct.cmbSubCategory.Text = dr.Cells(5).Value.ToString()
41 frmProduct.txtFeatures.Text = dr.Cells(6).Value.ToString()
42 frmProduct.txtCostPrice.Text = dr.Cells(7).Value.ToString()
43 frmProduct.txtSellingPrice.Text = dr.Cells(8).Value.ToString()
44 frmProduct.txtDiscount.Text = dr.Cells(9).Value.ToString()
45 frmProduct.txtVAT.Text = dr.Cells(10).Value.ToString()
46 frmProduct.txtReorderPoint.Text = dr.Cells(11).Value.ToString()
47 con = New SqlConnection(cs)
48 con.Open()
49 cmd = New SqlCommand("SELECT Photo from Product,Product_Join where Product.PID=Product_Join.ProductID and Product.PID=@d1", con)
50 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value.ToString())
51 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
52 frmProduct.dgw.Rows.Clear()
53 While (rdr.Read() = True)
54 Dim img4 As Image
55 Dim data As Byte() = DirectCast(rdr(0), Byte())
56 Dim ms As New MemoryStream(data)
57 img4 = Image.FromStream(ms)
58 frmProduct.dgw.Rows.Add(img4)
59 End While
60 con.Close()
61 frmProduct.btnUpdate.Enabled = True
62 frmProduct.btnDelete.Enabled = True
63 frmProduct.btnSave.Enabled = False
64 lblSet.Text = ""
65 End If
66 End If
67 If lblSet.Text = "Quotation" Then
68 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
69 frmQuotation.Show()
70 Me.Hide()
71 frmQuotation.txtProductID.Text = dr.Cells(0).Value.ToString()
72 frmQuotation.txtProductCode.Text = dr.Cells(1).Value.ToString()
73 frmQuotation.txtProductName.Text = dr.Cells(2).Value.ToString()
74 frmQuotation.txtSellingPrice.Text = dr.Cells(8).Value.ToString()
75 frmQuotation.txtDiscountPer.Text = dr.Cells(9).Value.ToString()
76 frmQuotation.txtVAT.Text = dr.Cells(10).Value.ToString()
77 lblSet.Text = ""
78 End If
79
80 If lblSet.Text = "Stock" Then
81 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
82 frmStock.Show()
83 Me.Hide()
84 frmStock.txtProductID.Text = dr.Cells(0).Value.ToString()
85 frmStock.txtProductCode.Text = dr.Cells(1).Value.ToString()
86 frmStock.txtProductName.Text = dr.Cells(2).Value.ToString()
87 lblSet.Text = ""
88 End If
89
90 Catch ex As Exception
91 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
92 End Try
93
94 End Sub
95
96 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
97 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
98 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
99 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
100 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
101 End If
102 Dim b As Brush = SystemBrushes.ControlText
103 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
104
105 End Sub
106 Sub Reset()
107 txtProductName.Text = ""
108 txtCategory.Text = ""
109 txtSubCategory.Text = ""
110 Getdata()
111 End Sub
112 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
113 Reset()
114 End Sub
115
116 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
117 Me.Close()
118 End Sub
119
120
121 Private Sub txtProductName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtProductName.TextChanged
122 Try
123 con = New SqlConnection(cs)
124 con.Open()
125 cmd = New SqlCommand("Select PID, RTRIM(ProductCode),RTRIM(Productname), SubCategoryID,RTRIM(CategoryName),RTRIM(SubCategoryName), RTRIM(Description), CostPrice,SellingPrice, Discount, VAT, ReorderPoint from Category,SubCategory,Product where Category.CategoryName=SubCategory.Category and Product.SubCategoryID=SubCategory.ID and ProductName like '%" & txtProductName.Text & "%' order by ProductName", con)
126 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
127 dgw.Rows.Clear()
128 While (rdr.Read() = True)
129 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11))
130 End While
131 con.Close()
132 Catch ex As Exception
133 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
134 End Try
135 End Sub
136
137 Private Sub txtCategory_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCategory.TextChanged
138 Try
139 con = New SqlConnection(cs)
140 con.Open()
141 cmd = New SqlCommand("Select PID, RTRIM(ProductCode),RTRIM(Productname), SubCategoryID,RTRIM(CategoryName),RTRIM(SubCategoryName), RTRIM(Description), CostPrice,SellingPrice, Discount, VAT, ReorderPoint from Category,SubCategory,Product where Category.CategoryName=SubCategory.Category and Product.SubCategoryID=SubCategory.ID and CategoryName like '%" & txtCategory.Text & "%' order by ProductName", con)
142 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
143 dgw.Rows.Clear()
144 While (rdr.Read() = True)
145 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11))
146 End While
147 con.Close()
148 Catch ex As Exception
149 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
150 End Try
151 End Sub
152
153 Private Sub txtSubCategory_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSubCategory.TextChanged
154 Try
155 con = New SqlConnection(cs)
156 con.Open()
157 cmd = New SqlCommand("Select PID, RTRIM(ProductCode),RTRIM(Productname), SubCategoryID,RTRIM(CategoryName),RTRIM(SubCategoryName), RTRIM(Description), CostPrice,SellingPrice, Discount, VAT, ReorderPoint from Category,SubCategory,Product where Category.CategoryName=SubCategory.Category and Product.SubCategoryID=SubCategory.ID and SubCategoryName like '%" & txtSubCategory.Text & "%' order by ProductName", con)
158 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
159 dgw.Rows.Clear()
160 While (rdr.Read() = True)
161 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11))
162 End While
163 con.Close()
164 Catch ex As Exception
165 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
166 End Try
167 End Sub
168
169 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
170 Dim rowsTotal, colsTotal As Short
171 Dim I, j, iC As Short
172 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
173 Dim xlApp As New Excel.Application
174 Try
175 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
176 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
177 xlApp.Visible = True
178
179 rowsTotal = dgw.RowCount
180 colsTotal = dgw.Columns.Count - 1
181 With excelWorksheet
182 .Cells.Select()
183 .Cells.Delete()
184 For iC = 0 To colsTotal
185 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
186 Next
187 For I = 0 To rowsTotal - 1
188 For j = 0 To colsTotal
189 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
190 Next j
191 Next I
192 .Rows("1:1").Font.FontStyle = "Bold"
193 .Rows("1:1").Font.Size = 12
194
195 .Cells.Columns.AutoFit()
196 .Cells.Select()
197 .Cells.EntireColumn.AutoFit()
198 .Cells(1, 1).Select()
199 End With
200 Catch ex As Exception
201 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
202 Finally
203 'RELEASE ALLOACTED RESOURCES
204 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
205 xlApp = Nothing
206 End Try
207 End Sub
208 End Class
